import Router from "koa-router";
import { ApiResponse } from "../../../types";
import { authMiddleware } from "../../../middleware/auth";
import { getChatManager } from "../../../websocket/server";
import si from "systeminformation";
import { pool } from "../../../config/database";
import { RowDataPacket } from "mysql2";
import axios from "axios";
import { config } from "../../../config/env";

const router = new Router({ prefix: "/api/admin/dashboard" });

// 管理员权限检查函数
const checkAdminPermission = (ctx: any) => {
  if (!ctx.state.user || ctx.state.user.role !== 'admin') {
    ctx.status = 403;
    ctx.body = {
      success: false,
      message: "需要管理员权限"
    } as ApiResponse;
    return false;
  }
  return true;
};

// 服务器系统信息缓存
let systemInfoCache: any = null;
let lastUpdateTime = 0;
const CACHE_DURATION = 30000; // 30秒缓存

// 获取服务器核心信息
async function getServerInfo() {
  const now = Date.now();
  
  // 如果缓存未过期，直接返回缓存
  if (systemInfoCache && (now - lastUpdateTime) < CACHE_DURATION) {
    return systemInfoCache;
  }

  try {
    // 并行获取所有系统信息
    const [
      cpuInfo,
      memInfo, 
      diskInfo,
      networkInfo,
      osInfo,
      currentLoad,
      processInfo,
      dockerInfo
    ] = await Promise.all([
      si.cpu(),
      si.mem(),
      si.fsSize(),
      si.networkInterfaces(),
      si.osInfo(),
      si.currentLoad(),
      si.processes(),
      si.dockerInfo().catch(() => null) // Docker可能不存在
    ]);

    // 获取数据库连接信息
    const dbStats = await getDatabaseStats();

    const serverInfo = {
      // 基本信息
      hostname: osInfo.hostname,
      platform: osInfo.platform,
      distro: osInfo.distro,
      arch: osInfo.arch,
      uptime: process.uptime(),
      
      // 系统说明
      systemNote: {
        description: "此监控显示的是运行Node.js服务器的机器信息",
        environment: process.env.NODE_ENV || 'development',
        isLocal: osInfo.hostname.includes('DESKTOP') || osInfo.hostname.includes('PC') || 
                osInfo.platform === 'win32' || osInfo.hostname.includes('localhost'),
        explanation: process.env.NODE_ENV === 'production' 
          ? "生产环境 - 显示服务器硬件信息" 
          : "开发环境 - 显示开发机器信息"
      },
      
      // CPU信息
      cpu: {
        manufacturer: cpuInfo.manufacturer,
        brand: cpuInfo.brand,
        cores: cpuInfo.cores,
        physicalCores: cpuInfo.physicalCores,
        speed: cpuInfo.speed,
        speedMax: cpuInfo.speedMax,
        load: {
          currentLoad: currentLoad.currentLoad,
          avgLoad: currentLoad.avgLoad,
          cpus: currentLoad.cpus?.map(cpu => ({
            load: cpu.load,
            loadUser: cpu.loadUser,
            loadSystem: cpu.loadSystem,
            loadIdle: cpu.loadIdle
          }))
        }
      },
      
      // 内存信息
      memory: {
        total: memInfo.total,
        free: memInfo.free,
        used: memInfo.used,
        available: memInfo.available,
        usagePercent: ((memInfo.used / memInfo.total) * 100).toFixed(2),
        swapTotal: memInfo.swaptotal,
        swapUsed: memInfo.swapused,
        swapFree: memInfo.swapfree
      },
      
      // 磁盘信息
      disk: diskInfo.map(disk => ({
        fs: disk.fs,
        type: disk.type,
        size: disk.size,
        used: disk.used,
        available: disk.available,
        usagePercent: disk.use,
        mount: disk.mount
      })),
      
      // 网络信息
      network: networkInfo
        .filter(net => !net.internal && net.ip4)
        .map(net => ({
          iface: net.iface,
          ip4: net.ip4,
          ip6: net.ip6,
          mac: net.mac,
          speed: net.speed,
          type: net.type
        })),
      
      // 进程信息
      processes: {
        all: processInfo.all,
        running: processInfo.running,
        blocked: processInfo.blocked,
        sleeping: processInfo.sleeping,
        list: processInfo.list
          ?.filter(proc => proc.cpu > 0)
          ?.sort((a, b) => b.cpu - a.cpu)
          ?.slice(0, 10)
          ?.map(proc => ({
            pid: proc.pid,
            name: proc.name,
            command: proc.command,
            cpu: proc.cpu,
            mem: proc.mem,
            state: proc.state
          }))
      },
      
      // 数据库信息
      database: dbStats,
      
      // Docker信息（如果可用）
      docker: dockerInfo ? {
        containers: dockerInfo.containers,
        containersRunning: dockerInfo.containersRunning,
        containersPaused: dockerInfo.containersPaused,
        containersStopped: dockerInfo.containersStopped,
        images: dockerInfo.images
      } : null,
      
      // 时间戳
      timestamp: now,
      updateTime: new Date().toISOString()
    };

    // 更新缓存
    systemInfoCache = serverInfo;
    lastUpdateTime = now;
    
    return serverInfo;
    
  } catch (error) {
    console.error('获取系统信息失败:', error);
    throw error;
  }
}

// 获取数据库统计信息
async function getDatabaseStats() {
  try {
    const [statusResult] = await pool.execute('SHOW STATUS') as [RowDataPacket[], any];
    const [variablesResult] = await pool.execute('SHOW VARIABLES LIKE "max_connections"') as [RowDataPacket[], any];
    const [processListResult] = await pool.execute('SHOW PROCESSLIST') as [RowDataPacket[], any];
    
    const statusMap: any = {};
    statusResult.forEach((row: any) => {
      statusMap[row.Variable_name] = row.Value;
    });
    
    const maxConnections = parseInt(variablesResult[0]?.Value || '0');
    const currentConnections = processListResult.length;
    
    return {
      connections: {
        current: currentConnections,
        max: maxConnections,
        usagePercent: maxConnections > 0 ? ((currentConnections / maxConnections) * 100).toFixed(2) : '0'
      },
      queries: {
        total: parseInt(statusMap.Queries || '0'),
        perSecond: parseFloat(statusMap.Queries_per_second_avg || '0'),
        slow: parseInt(statusMap.Slow_queries || '0')
      },
      tables: {
        openTables: parseInt(statusMap.Open_tables || '0'),
        openedTables: parseInt(statusMap.Opened_tables || '0')
      },
      innodb: {
        bufferPoolSize: parseInt(statusMap.Innodb_buffer_pool_size || '0'),
        bufferPoolPages: parseInt(statusMap.Innodb_buffer_pool_pages_total || '0'),
        bufferPoolFree: parseInt(statusMap.Innodb_buffer_pool_pages_free || '0'),
        dataRead: parseInt(statusMap.Innodb_data_read || '0'),
        dataWritten: parseInt(statusMap.Innodb_data_written || '0')
      },
      uptime: parseInt(statusMap.Uptime || '0')
    };
  } catch (error) {
    console.error('获取数据库统计失败:', error);
    return {
      error: '无法获取数据库统计信息',
      message: error instanceof Error ? error.message : '未知错误'
    };
  }
}

// 获取DeepSeek余额信息
async function getDeepSeekBalance() {
  try {
    console.log('🔍 正在查询DeepSeek余额...');
    
    const response = await axios.get(
      `${config.deepseek.base_url}/user/balance`,
      {
        headers: {
          'Authorization': `Bearer ${config.deepseek.apiKey}`,
          'Content-Type': 'application/json'
        },
        timeout: 10000
      }
    );

    if (response.data) {
      console.log('✅ DeepSeek余额查询成功');
      
      const balanceData = response.data;
      const balanceInfos = balanceData.balance_infos || [];
      
      // 处理多币种余额
      const processedBalance = {
        is_available: balanceData.is_available || false,
        currencies: balanceInfos.map((info: any) => ({
          currency: info.currency,
          total_balance: parseFloat(info.total_balance || '0'),
          granted_balance: parseFloat(info.granted_balance || '0'),
          topped_up_balance: parseFloat(info.topped_up_balance || '0'),
          balance_display: `${info.total_balance} ${info.currency}`
        })),
        // 计算总余额（以人民币为主）
        total_balance_cny: balanceInfos.find((info: any) => info.currency === 'CNY')?.total_balance || '0',
        total_balance_usd: balanceInfos.find((info: any) => info.currency === 'USD')?.total_balance || '0',
        status: balanceData.is_available ? 'sufficient' : 'insufficient',
        last_updated: new Date().toISOString(),
        api_status: 'success'
      };

      return processedBalance;
    } else {
      console.log('❌ DeepSeek余额查询返回空数据');
      return {
        is_available: false,
        currencies: [],
        total_balance_cny: '0',
        total_balance_usd: '0',
        status: 'unknown',
        error: '余额数据为空',
        api_status: 'error',
        last_updated: new Date().toISOString()
      };
    }
  } catch (error: any) {
    console.error('❌ DeepSeek余额查询失败:', error.message);
    
    let errorInfo = {
      is_available: false,
      currencies: [],
      total_balance_cny: 'N/A',
      total_balance_usd: 'N/A',
      status: 'error',
      api_status: 'error',
      last_updated: new Date().toISOString(),
      error: '余额查询失败',
      error_details: error.message
    };

    // 详细错误处理
    if (error.response) {
      errorInfo.error = `API错误: ${error.response.status} ${error.response.statusText}`;
      errorInfo.error_details = error.response.data?.message || error.response.data?.error || '未知API错误';
    } else if (error.code === 'ECONNREFUSED') {
      errorInfo.error = 'DeepSeek服务连接被拒绝';
      errorInfo.error_details = '可能是网络问题或API服务不可用';
    } else if (error.code === 'ETIMEDOUT') {
      errorInfo.error = 'DeepSeek API请求超时';
      errorInfo.error_details = '网络连接超时，请稍后重试';
    } else if (error.message.includes('API key')) {
      errorInfo.error = 'DeepSeek API密钥错误';
      errorInfo.error_details = '请检查API密钥配置';
    }

    return errorInfo;
  }
}

// 获取DeepSeek使用统计（基于本地数据库）
async function getDeepSeekUsageStats(timeParams: any[]) {
  try {
    // 从AI聊天记录中统计使用情况
    const [usageStats] = await pool.execute(`
      SELECT 
        COUNT(*) as total_requests,
        COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY) THEN 1 END) as requests_today,
        COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as requests_week,
        COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as requests_month,
        COUNT(CASE WHEN created_at BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) AND NOW() THEN 1 END) as requests_in_range,
        COUNT(DISTINCT user_id) as unique_users,
        COUNT(DISTINCT session_id) as unique_sessions,
        MIN(created_at) as first_request,
        MAX(created_at) as last_request
      FROM ai_chat_records 
      WHERE message_type = 'user'
    `, [timeParams[0] || 30]) as [RowDataPacket[], any];

    return {
      ...usageStats[0],
      avg_requests_per_user: usageStats[0].unique_users > 0 
        ? (usageStats[0].total_requests / usageStats[0].unique_users).toFixed(2)
        : 0,
      avg_requests_per_session: usageStats[0].unique_sessions > 0
        ? (usageStats[0].total_requests / usageStats[0].unique_sessions).toFixed(2)
        : 0
    };
  } catch (error) {
    console.error('DeepSeek使用统计查询失败:', error);
    return {
      total_requests: 0,
      requests_today: 0,
      requests_week: 0,
      requests_month: 0,
      requests_in_range: 0,
      unique_users: 0,
      unique_sessions: 0,
      avg_requests_per_user: 0,
      avg_requests_per_session: 0,
      error: '统计数据获取失败'
    };
  }
}

// 获取服务器信息接口
router.get("/server-info", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const serverInfo = await getServerInfo();
    
    ctx.body = {
      success: true,
      data: serverInfo,
      message: "获取服务器信息成功"
    } as ApiResponse;
    
  } catch (error: any) {
    console.error("获取服务器信息失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "获取服务器信息失败",
      error: error.message
    } as ApiResponse;
  }
});

// 监控定时器存储
const monitorTimers = new Map<number, NodeJS.Timeout>();
// 监控启动时间记录，防止频繁重启
const monitorStartTimes = new Map<number, number>();

// 启动实时服务器监控
router.post("/start-monitor", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  const userId = ctx.state.user.id as number;
  const chatManager = getChatManager();
  
  try {
    // 检查是否刚刚启动过监控（防止频繁重启）
    const lastStartTime = monitorStartTimes.get(userId);
    const now = Date.now();
    if (lastStartTime && (now - lastStartTime) < 5000) { // 5秒内不允许重复启动
      ctx.status = 429;
      ctx.body = {
        success: false,
        message: `请等待 ${Math.ceil((5000 - (now - lastStartTime)) / 1000)} 秒后再启动监控`
      } as ApiResponse;
      return;
    }
    
    // 记录启动时间
    monitorStartTimes.set(userId, now);
    
    // 检查用户是否在线
    const isOnline = chatManager.isUserOnline(userId);
    console.log(`🔍 检查用户 ${userId} 在线状态:`, isOnline);
    
    if (!isOnline) {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: "请先连接WebSocket"
      } as ApiResponse;
      return;
    }

    // 发送初始服务器信息
    console.log(`📊 获取初始服务器信息...`);
    const serverInfo = await getServerInfo();
    console.log(`📊 服务器信息获取完成，CPU负载: ${serverInfo.cpu.load.currentLoad}%`);
    
    const success = chatManager.sendToUserId(userId, {
      type: 'server_monitor_start',
      data: {
        message: '服务器监控已启动',
        serverInfo,
        monitorId: `monitor_${userId}_${Date.now()}`
      }
    });

    console.log(`🚀 初始监控消息发送结果:`, success);

    if (success) {
      // 启动定时推送 (每10秒)
      startServerMonitoring(userId);
      
      ctx.body = {
        success: true,
        message: "服务器监控已启动",
        data: { monitorStarted: true }
      } as ApiResponse;
    } else {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: "WebSocket连接失败"
      } as ApiResponse;
    }
    
  } catch (error: any) {
    console.error("启动服务器监控失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "启动监控失败",
      error: error.message
    } as ApiResponse;
  }
});

// 启动服务器监控推送
function startServerMonitoring(userId: number) {
  // 清除已存在的定时器
  if (monitorTimers.has(userId)) {
    console.log(`🔄 清除用户 ${userId} 的旧监控定时器`);
    clearInterval(monitorTimers.get(userId)!);
  }
  
  const chatManager = getChatManager();
  
  // 创建新的定时器 - 改为10秒间隔
  const timer = setInterval(async () => {
    try {
      // 检查用户是否仍然在线
      if (!chatManager.isUserOnline(userId)) {
        console.log(`❌ 用户 ${userId} 已离线，停止监控`);
        clearInterval(timer);
        monitorTimers.delete(userId);
        // 清理启动时间记录
        monitorStartTimes.delete(userId);
        return;
      }
      
      // 获取最新服务器信息
      console.log(`📊 获取用户 ${userId} 的实时服务器信息...`);
      const serverInfo = await getServerInfo();
      
      // 推送更新
      const sendResult = chatManager.sendToUserId(userId, {
        type: 'server_monitor_update',
        data: {
          serverInfo,
          timestamp: Date.now()
        }
      });
      
      if (sendResult) {
        console.log(`✅ 用户 ${userId} 服务器监控信息推送成功 - CPU: ${serverInfo.cpu.load.currentLoad}%, 内存: ${serverInfo.memory.usagePercent}%`);
      } else {
        console.log(`❌ 用户 ${userId} 服务器监控信息推送失败`);
        
        // 获取WebSocket连接状态详情
        const stats = chatManager.getDetailedStats();
        console.log(`📊 当前WebSocket连接详情:`, JSON.stringify(stats, null, 2));
      }
      
    } catch (error) {
      console.error(`❌ 推送服务器监控信息失败 (用户 ${userId}):`, error);
    }
  }, 10000); // 改为10秒间隔
  
  monitorTimers.set(userId, timer);
  console.log(`🎯 为用户 ${userId} 启动服务器监控推送 (每10秒更新一次)`);
}

// 停止监控
router.post("/stop-monitor", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  const userId = ctx.state.user.id as number;
  
  if (monitorTimers.has(userId)) {
    clearInterval(monitorTimers.get(userId)!);
    monitorTimers.delete(userId);
    
    // 清理启动时间记录
    monitorStartTimes.delete(userId);
    
    // 通知前端监控已停止
    const chatManager = getChatManager();
    chatManager.sendToUserId(userId, {
      type: 'server_monitor_stop',
      data: {
        message: '服务器监控已停止',
        timestamp: Date.now()
      }
    });
  }
  
  ctx.body = {
    success: true,
    message: "监控已停止"
  } as ApiResponse;
});

// 获取应用统计信息
router.get("/app-stats", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    // 获取查询参数
    const { 
      startDate = null, 
      endDate = null, 
      timeRange = '30' // 默认30天
    } = ctx.query;
    
    // 构建时间条件
    let timeCondition = '';
    let timeParams: any[] = [];
    
    if (startDate && endDate) {
      timeCondition = 'AND created_at BETWEEN ? AND ?';
      timeParams = [startDate, endDate];
    } else {
      timeCondition = 'AND created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)';
      timeParams = [parseInt(timeRange as string)];
    }

    // 并行获取所有统计数据，包括DeepSeek余额
    const [
      userStatsResult,
      orderStatsResult,
      productStatsResult,
      popularProductsResult,
      aiChatStatsResult,
      creditStatsResult,
      dailyTrendsResult,
      ipLocationStatsResult,
      provinceStatsResult,
      cityStatsResult,
      userLocationStatsResult,
      ipHotSpotsResult,
      mapVisualizationResult,
      deepseekBalance,
      deepseekUsageStats
    ] = await Promise.all([
      // 获取用户统计（只统计基本用户数量，不依赖role字段）
      pool.execute(`
        SELECT 
          COUNT(*) as total_users,
          0 as admin_users,
          COUNT(*) as regular_users,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY) THEN 1 END) as new_users_today,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as new_users_week,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as new_users_month,
          0 as active_users_today,
          0 as active_users_week,
          COUNT(CASE WHEN created_at BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) AND NOW() THEN 1 END) as users_in_range
        FROM users
      `, [timeParams[0] || 30]),
      
      // 获取订单统计
      pool.execute(`
        SELECT 
          COUNT(*) as total_orders,
          COUNT(CASE WHEN payment_status = 'paid' THEN 1 END) as paid_orders,
          COUNT(CASE WHEN payment_status = 'pending' THEN 1 END) as pending_orders,
          COUNT(CASE WHEN payment_status = 'failed' THEN 1 END) as failed_orders,
          COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_orders,
          COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled_orders,
          SUM(CASE WHEN payment_status = 'paid' THEN final_price ELSE 0 END) as total_revenue,
          SUM(CASE WHEN payment_status = 'paid' AND created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY) THEN final_price ELSE 0 END) as revenue_today,
          SUM(CASE WHEN payment_status = 'paid' AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN final_price ELSE 0 END) as revenue_week,
          SUM(CASE WHEN payment_status = 'paid' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN final_price ELSE 0 END) as revenue_month,
          AVG(CASE WHEN payment_status = 'paid' THEN final_price END) as avg_order_value,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY) THEN 1 END) as orders_today,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as orders_week,
          SUM(CASE WHEN payment_status = 'paid' AND created_at BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) AND NOW() THEN final_price ELSE 0 END) as revenue_in_range,
          COUNT(CASE WHEN created_at BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) AND NOW() THEN 1 END) as orders_in_range
        FROM orders
      `, [timeParams[0] || 30, timeParams[0] || 30]),

      // 获取产品统计
      pool.execute(`
        SELECT 
          COUNT(*) as total_products,
          0 as featured_products,
          0 as recommended_products,
          COUNT(CASE WHEN status = 'active' THEN 1 END) as active_products,
          COUNT(CASE WHEN status = 'inactive' THEN 1 END) as inactive_products,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as new_products_week,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as new_products_month,
          AVG(price) as avg_price,
          SUM(stock) as total_stock,
          COUNT(CASE WHEN stock <= 10 THEN 1 END) as low_stock_products
        FROM products
      `),

      // 获取热门产品
      pool.execute(`
        SELECT 
          p.id,
          p.name,
          p.price,
          0 as is_featured,
          0 as is_recommended,
          COUNT(o.id) as order_count,
          SUM(CASE WHEN o.payment_status = 'paid' THEN o.final_price ELSE 0 END) as total_revenue
        FROM products p
        LEFT JOIN orders o ON p.id = o.product_id 
        WHERE p.status = 'active'
        GROUP BY p.id, p.name, p.price
        ORDER BY order_count DESC, total_revenue DESC
        LIMIT 10
      `),

      // 获取AI聊天统计
      pool.execute(`
        SELECT 
          COUNT(*) as total_messages,
          COUNT(DISTINCT user_id) as active_chat_users,
          COUNT(DISTINCT session_id) as total_sessions,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY) THEN 1 END) as messages_today,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as messages_week,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as messages_month,
          COUNT(CASE WHEN message_type = 'user' THEN 1 END) as user_messages,
          COUNT(CASE WHEN message_type = 'assistant' THEN 1 END) as ai_responses,
          COUNT(CASE WHEN created_at BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) AND NOW() THEN 1 END) as messages_in_range,
          COUNT(CASE WHEN message_type = 'user' AND created_at BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) AND NOW() THEN 1 END) as user_messages_in_range
        FROM ai_chat_records
      `, [timeParams[0] || 30, timeParams[0] || 30]),

      // 获取AI额度统计（DeepSeek的额度通过API获取，这里不查询用户credits）
      pool.execute(`
        SELECT 
          0 as total_credits,
          0 as users_with_credits,
          0 as avg_credits_per_user,
          0 as low_credit_users,
          COUNT(*) as zero_credit_users,
          0 as max_credits,
          0 as min_credits
        FROM users
        LIMIT 1
      `),

      // 获取每日统计趋势
      pool.execute(`
        SELECT 
          DATE(created_at) as date,
          COUNT(CASE WHEN table_name = 'users' THEN 1 END) as new_users,
          COUNT(CASE WHEN table_name = 'orders' THEN 1 END) as new_orders,
          COUNT(CASE WHEN table_name = 'ai_chat_records' THEN 1 END) as ai_messages
        FROM (
          SELECT created_at, 'users' as table_name FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
          UNION ALL
          SELECT created_at, 'orders' as table_name FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
          UNION ALL
          SELECT created_at, 'ai_chat_records' as table_name FROM ai_chat_records WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        ) combined
        GROUP BY DATE(created_at)
        ORDER BY date DESC
        LIMIT 30
      `),

      // IP地理位置统计及其他查询...
      pool.execute(`
        SELECT 
          COUNT(DISTINCT ip) as total_unique_ips,
          COUNT(*) as total_ip_records,
          COUNT(CASE WHEN province IS NOT NULL AND province != '' THEN 1 END) as located_ips,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY) THEN 1 END) as new_ips_today,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as new_ips_week,
          COUNT(CASE WHEN created_at BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) AND NOW() THEN 1 END) as ips_in_range
        FROM ip_addresses
      `, [timeParams[0] || 30]),

      pool.execute(`
        SELECT 
          province,
          COUNT(DISTINCT ip) as unique_ips,
          COUNT(*) as total_records,
          COUNT(DISTINCT userId) as unique_users,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as recent_records,
          MIN(created_at) as first_seen,
          MAX(created_at) as last_seen
        FROM ip_addresses 
        WHERE province IS NOT NULL AND province != ''
        GROUP BY province
        ORDER BY unique_ips DESC, total_records DESC
        LIMIT 20
      `),

      pool.execute(`
        SELECT 
          province,
          city,
          COUNT(DISTINCT ip) as unique_ips,
          COUNT(*) as total_records,
          COUNT(DISTINCT userId) as unique_users,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as recent_records,
          adcode
        FROM ip_addresses 
        WHERE city IS NOT NULL AND city != '' AND province IS NOT NULL
        GROUP BY province, city, adcode
        ORDER BY unique_ips DESC, total_records DESC
        LIMIT 30
      `),

      pool.execute(`
        SELECT 
          '未知' as province,
          '未知' as city,
          0 as user_count,
          0 as new_users_month,
          0 as active_users_week
        FROM users
        LIMIT 1
      `),

      pool.execute(`
        SELECT 
          ip.province,
          ip.city,
          ip.adcode,
          COUNT(*) as access_count,
          COUNT(DISTINCT ip.ip) as unique_ips,
          COUNT(DISTINCT ip.userId) as unique_users,
          AVG(CASE WHEN ip.userId IS NOT NULL THEN 1 ELSE 0 END) as login_rate
        FROM ip_addresses ip
        WHERE ip.province IS NOT NULL AND ip.province != ''
          AND ip.created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
        GROUP BY ip.province, ip.city, ip.adcode
        HAVING access_count >= 5
        ORDER BY access_count DESC, unique_users DESC
        LIMIT 100
      `, [timeParams[0] || 30]),

      pool.execute(`
        SELECT 
          province,
          COUNT(DISTINCT ip) as unique_ips,
          COUNT(*) as total_visits,
          COUNT(DISTINCT userId) as unique_users,
          COUNT(CASE WHEN userId IS NOT NULL THEN 1 END) as logged_visits,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as recent_visits,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as month_visits,
          GROUP_CONCAT(DISTINCT city ORDER BY city SEPARATOR ',') as cities,
          MIN(created_at) as first_visit,
          MAX(created_at) as last_visit
        FROM ip_addresses
        WHERE province IS NOT NULL AND province != ''
        GROUP BY province
        ORDER BY unique_users DESC, total_visits DESC
      `),

      // 获取DeepSeek余额信息
      getDeepSeekBalance(),

      // 获取DeepSeek使用统计
      getDeepSeekUsageStats(timeParams)
    ]);

    // 处理地图可视化数据，添加更多维度
    const mapData = (mapVisualizationResult as [RowDataPacket[], any])[0].map((item: any) => ({
      ...item,
      visit_density: item.total_visits / (item.unique_ips || 1), // 平均每IP访问次数
      user_conversion_rate: item.unique_users > 0 ? ((item.unique_users / item.unique_ips) * 100).toFixed(2) : '0', // 用户转化率
      recent_activity: item.recent_visits / (item.month_visits || 1), // 近期活跃度
      city_count: item.cities ? item.cities.split(',').length : 0,
      cities_array: item.cities ? item.cities.split(',') : []
    }));

    // 获取WebSocket连接统计
    const chatManager = getChatManager();
    const wsStats = chatManager.getStats();
    
    const appStats = {
      // 用户统计
      users: {
        ...(userStatsResult as [RowDataPacket[], any])[0][0],
        growth_rate: (userStatsResult as [RowDataPacket[], any])[0][0].new_users_month > 0 && (userStatsResult as [RowDataPacket[], any])[0][0].total_users > 0 
          ? (((userStatsResult as [RowDataPacket[], any])[0][0].new_users_month / (userStatsResult as [RowDataPacket[], any])[0][0].total_users) * 100).toFixed(2) 
          : 0
      },
      
      // 订单统计
      orders: {
        ...(orderStatsResult as [RowDataPacket[], any])[0][0],
        payment_success_rate: (orderStatsResult as [RowDataPacket[], any])[0][0].total_orders > 0 
          ? (((orderStatsResult as [RowDataPacket[], any])[0][0].paid_orders / (orderStatsResult as [RowDataPacket[], any])[0][0].total_orders) * 100).toFixed(2)
          : 0,
        completion_rate: (orderStatsResult as [RowDataPacket[], any])[0][0].total_orders > 0
          ? (((orderStatsResult as [RowDataPacket[], any])[0][0].completed_orders / (orderStatsResult as [RowDataPacket[], any])[0][0].total_orders) * 100).toFixed(2)
          : 0
      },
      
      // 产品统计
      products: {
        ...(productStatsResult as [RowDataPacket[], any])[0][0],
        featured_rate: (productStatsResult as [RowDataPacket[], any])[0][0].total_products > 0
          ? (((productStatsResult as [RowDataPacket[], any])[0][0].featured_products / (productStatsResult as [RowDataPacket[], any])[0][0].total_products) * 100).toFixed(2)
          : 0,
        popular_products: (popularProductsResult as [RowDataPacket[], any])[0]
      },
      
      // AI聊天统计
      aiChat: {
        ...(aiChatStatsResult as [RowDataPacket[], any])[0][0],
        avg_messages_per_user: (aiChatStatsResult as [RowDataPacket[], any])[0][0].active_chat_users > 0
          ? ((aiChatStatsResult as [RowDataPacket[], any])[0][0].total_messages / (aiChatStatsResult as [RowDataPacket[], any])[0][0].active_chat_users).toFixed(2)
          : 0,
        avg_messages_per_session: (aiChatStatsResult as [RowDataPacket[], any])[0][0].total_sessions > 0
          ? ((aiChatStatsResult as [RowDataPacket[], any])[0][0].total_messages / (aiChatStatsResult as [RowDataPacket[], any])[0][0].total_sessions).toFixed(2)
          : 0
      },
      
      // AI额度统计
      credits: (creditStatsResult as [RowDataPacket[], any])[0][0] || {
        total_credits: 0,
        users_with_credits: 0,
        avg_credits_per_user: 0,
        low_credit_users: 0,
        zero_credit_users: 0,
        max_credits: 0,
        min_credits: 0
      },

      // DeepSeek统计（新增）
      deepseek: {
        // 余额信息
        balance: deepseekBalance,
        // 使用统计
        usage: deepseekUsageStats,
        // 配置信息
        config: {
          model: config.deepseek.model,
          base_url: config.deepseek.base_url,
          api_key_masked: config.deepseek.apiKey ? 
            config.deepseek.apiKey.substring(0, 8) + '...' + config.deepseek.apiKey.substring(config.deepseek.apiKey.length - 4) 
            : 'N/A'
        }
      },

      // IP地理位置统计
      ipGeoLocation: {
        // 基础统计
        overview: {
          ...(ipLocationStatsResult as [RowDataPacket[], any])[0][0],
          location_rate: (ipLocationStatsResult as [RowDataPacket[], any])[0][0].total_unique_ips > 0 
            ? (((ipLocationStatsResult as [RowDataPacket[], any])[0][0].located_ips / (ipLocationStatsResult as [RowDataPacket[], any])[0][0].total_unique_ips) * 100).toFixed(2)
            : 0
        },
        
        // 省份分布（TOP20）
        provinces: (provinceStatsResult as [RowDataPacket[], any])[0].map((item: any) => ({
          ...item,
          user_conversion_rate: item.unique_ips > 0 ? ((item.unique_users / item.unique_ips) * 100).toFixed(2) : '0'
        })),
        
        // 城市分布（TOP30）  
        cities: (cityStatsResult as [RowDataPacket[], any])[0].map((item: any) => ({
          ...item,
          user_conversion_rate: item.unique_ips > 0 ? ((item.unique_users / item.unique_ips) * 100).toFixed(2) : '0',
          location_key: `${item.province}-${item.city}`
        })),
        
        // 用户地理分布
        userDistribution: (userLocationStatsResult as [RowDataPacket[], any])[0].map((item: any) => ({
          ...item,
          activity_rate: item.user_count > 0 ? ((item.active_users_week / item.user_count) * 100).toFixed(2) : '0',
          location_key: `${item.province}-${item.city || '未知'}`
        })),
        
        // 访问热点
        hotSpots: (ipHotSpotsResult as [RowDataPacket[], any])[0].map((item: any) => ({
          ...item,
          avg_visits_per_ip: (item.access_count / item.unique_ips).toFixed(2),
          login_rate_percent: (item.login_rate * 100).toFixed(2)
        })),
        
        // 中国地图可视化数据
        mapVisualization: {
          provinces: mapData,
          summary: {
            total_provinces: mapData.length,
            total_visits: mapData.reduce((sum: number, item: any) => sum + item.total_visits, 0),
            total_users: mapData.reduce((sum: number, item: any) => sum + item.unique_users, 0),
            most_active_province: mapData[0]?.province || null,
            least_active_province: mapData[mapData.length - 1]?.province || null
          }
        }
      },
      
      // 每日趋势
      dailyTrends: (dailyTrendsResult as [RowDataPacket[], any])[0],
      
      // WebSocket连接统计
      websocket: wsStats,
      
      // 服务器信息
      server: {
        nodeVersion: process.version,
        platform: process.platform,
        arch: process.arch,
        pid: process.pid,
        uptime: process.uptime(),
        memoryUsage: process.memoryUsage()
      },
      
      // 查询参数
      queryInfo: {
        timeRange: timeRange,
        startDate: startDate,
        endDate: endDate,
        generatedAt: new Date().toISOString()
      }
    };
    
    ctx.body = {
      success: true,
      data: appStats,
      message: "获取应用统计成功"
    } as ApiResponse;
    
  } catch (error: any) {
    console.error("获取应用统计失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "获取应用统计失败",
      error: error.message
    } as ApiResponse;
  }
});

// 获取特定时间范围的详细统计
router.get("/stats/detailed", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { 
      startDate, 
      endDate, 
      groupBy = 'day' // day, week, month
    } = ctx.query;
    
    if (!startDate || !endDate) {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: "请提供开始时间和结束时间"
      } as ApiResponse;
      return;
    }

    // 根据 groupBy 参数构建时间分组
    let dateFormat = '%Y-%m-%d';
    let dateInterval = 'DAY';
    
    switch (groupBy) {
      case 'week':
        dateFormat = '%Y-%u';
        dateInterval = 'WEEK';
        break;
      case 'month':
        dateFormat = '%Y-%m';
        dateInterval = 'MONTH';
        break;
      default:
        dateFormat = '%Y-%m-%d';
        dateInterval = 'DAY';
    }

    // 获取时间范围内的详细统计
    const [detailedStatsResult] = await pool.execute(`
      SELECT 
        DATE_FORMAT(date_key, ?) as period,
        SUM(new_users) as new_users,
        SUM(new_orders) as new_orders,
        SUM(paid_orders) as paid_orders,
        SUM(order_revenue) as order_revenue,
        SUM(ai_messages) as ai_messages,
        SUM(new_products) as new_products
      FROM (
        SELECT 
          DATE(created_at) as date_key,
          COUNT(*) as new_users,
          0 as new_orders,
          0 as paid_orders,
          0 as order_revenue,
          0 as ai_messages,
          0 as new_products
        FROM users 
        WHERE created_at BETWEEN ? AND ?
        GROUP BY DATE(created_at)
        
        UNION ALL
        
        SELECT 
          DATE(created_at) as date_key,
          0 as new_users,
          COUNT(*) as new_orders,
          COUNT(CASE WHEN payment_status = 'paid' THEN 1 END) as paid_orders,
          SUM(CASE WHEN payment_status = 'paid' THEN final_price ELSE 0 END) as order_revenue,
          0 as ai_messages,
          0 as new_products
        FROM orders 
        WHERE created_at BETWEEN ? AND ?
        GROUP BY DATE(created_at)
        
        UNION ALL
        
        SELECT 
          DATE(created_at) as date_key,
          0 as new_users,
          0 as new_orders,
          0 as paid_orders,
          0 as order_revenue,
          COUNT(*) as ai_messages,
          0 as new_products
        FROM ai_chat_records 
        WHERE created_at BETWEEN ? AND ? AND message_type = 'user'
        GROUP BY DATE(created_at)
        
        UNION ALL
        
        SELECT 
          DATE(created_at) as date_key,
          0 as new_users,
          0 as new_orders,
          0 as paid_orders,
          0 as order_revenue,
          0 as ai_messages,
          COUNT(*) as new_products
        FROM products 
        WHERE created_at BETWEEN ? AND ?
        GROUP BY DATE(created_at)
      ) combined
      GROUP BY date_key
      ORDER BY date_key DESC
    `, [dateFormat, startDate, endDate, startDate, endDate, startDate, endDate, startDate, endDate]) as [RowDataPacket[], any];

    ctx.body = {
      success: true,
      data: {
        statistics: detailedStatsResult,
        queryInfo: {
          startDate,
          endDate,
          groupBy,
          generatedAt: new Date().toISOString()
        }
      },
      message: "获取详细统计成功"
    } as ApiResponse;
    
  } catch (error: any) {
    console.error("获取详细统计失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "获取详细统计失败",
      error: error.message
    } as ApiResponse;
  }
});

// 获取IP地理分布数据（专为中国地图可视化设计）
router.get("/geo/china-map", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { 
      timeRange = '30',
      startDate = null, 
      endDate = null,
      level = 'province', // province, city, detail
      minVisits = 1
    } = ctx.query;
    
    // 构建时间条件
    let timeCondition = '';
    let timeParams: any[] = [];
    
    if (startDate && endDate) {
      timeCondition = 'WHERE created_at BETWEEN ? AND ?';
      timeParams = [startDate, endDate];
    } else {
      timeCondition = 'WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)';
      timeParams = [parseInt(timeRange as string)];
    }

    if (level === 'province') {
      // 省份级别数据 - 使用ip_addresses表
      const [provinceMapData] = await pool.execute(`
        SELECT 
          province,
          COUNT(DISTINCT ip) as unique_ips,
          COUNT(*) as total_visits,
          COUNT(DISTINCT userId) as unique_users,
          COUNT(CASE WHEN userId IS NOT NULL THEN 1 END) as logged_visits,
          COUNT(DISTINCT city) as city_count,
          GROUP_CONCAT(DISTINCT adcode ORDER BY adcode SEPARATOR ',') as adcodes,
          MIN(created_at) as first_visit,
          MAX(created_at) as last_visit,
          AVG(CASE WHEN userId IS NOT NULL THEN 1 ELSE 0 END) as login_rate
        FROM ip_addresses
        ${timeCondition} AND province IS NOT NULL AND province != ''
        GROUP BY province
        HAVING total_visits >= ?
        ORDER BY unique_users DESC, total_visits DESC
      `, [...timeParams, parseInt(minVisits as string)]) as [RowDataPacket[], any];

      // 计算统计指标
      const processedData = provinceMapData.map((item: any) => ({
        name: item.province, // 地图组件通常需要name字段
        province: item.province,
        value: item.unique_users, // 地图显示的主要数值
        visits: item.total_visits,
        unique_ips: item.unique_ips,
        unique_users: item.unique_users,
        logged_visits: item.logged_visits,
        city_count: item.city_count,
        login_rate: parseFloat((item.login_rate * 100).toFixed(2)),
        visit_density: parseFloat((item.total_visits / item.unique_ips).toFixed(2)),
        user_conversion_rate: parseFloat(((item.unique_users / item.unique_ips) * 100).toFixed(2)),
        adcodes: item.adcodes ? item.adcodes.split(',') : [],
        first_visit: item.first_visit,
        last_visit: item.last_visit
      }));

      ctx.body = {
        success: true,
        data: {
          level: 'province',
          mapData: processedData,
          summary: {
            total_provinces: processedData.length,
            total_visits: processedData.reduce((sum: number, item: any) => sum + item.visits, 0),
            total_users: processedData.reduce((sum: number, item: any) => sum + item.unique_users, 0),
            total_unique_ips: processedData.reduce((sum: number, item: any) => sum + item.unique_ips, 0),
            avg_login_rate: parseFloat((processedData.reduce((sum: number, item: any) => sum + item.login_rate, 0) / processedData.length).toFixed(2)),
            most_active_province: processedData[0]?.name || null,
            least_active_province: processedData[processedData.length - 1]?.name || null
          },
          queryInfo: {
            timeRange,
            startDate,
            endDate,
            level,
            minVisits,
            generatedAt: new Date().toISOString()
          }
        },
        message: "获取省份地图数据成功"
      } as ApiResponse;

    } else if (level === 'city') {
      // 城市级别数据 - 使用ip_addresses表
      const [cityMapData] = await pool.execute(`
        SELECT 
          province,
          city,
          adcode,
          COUNT(DISTINCT ip) as unique_ips,
          COUNT(*) as total_visits,
          COUNT(DISTINCT userId) as unique_users,
          COUNT(CASE WHEN userId IS NOT NULL THEN 1 END) as logged_visits,
          MIN(created_at) as first_visit,
          MAX(created_at) as last_visit,
          AVG(CASE WHEN userId IS NOT NULL THEN 1 ELSE 0 END) as login_rate
        FROM ip_addresses
        ${timeCondition} AND province IS NOT NULL AND city IS NOT NULL AND province != '' AND city != ''
        GROUP BY province, city, adcode
        HAVING total_visits >= ?
        ORDER BY unique_users DESC, total_visits DESC
        LIMIT 200
      `, [...timeParams, parseInt(minVisits as string)]) as [RowDataPacket[], any];

      const processedCityData = cityMapData.map((item: any) => ({
        name: `${item.province}-${item.city}`, // 地图组件的唯一标识
        province: item.province,
        city: item.city,
        adcode: item.adcode,
        value: item.unique_users,
        visits: item.total_visits,
        unique_ips: item.unique_ips,
        unique_users: item.unique_users,
        logged_visits: item.logged_visits,
        login_rate: parseFloat((item.login_rate * 100).toFixed(2)),
        visit_density: parseFloat((item.total_visits / item.unique_ips).toFixed(2)),
        user_conversion_rate: parseFloat(((item.unique_users / item.unique_ips) * 100).toFixed(2)),
        first_visit: item.first_visit,
        last_visit: item.last_visit
      }));

      ctx.body = {
        success: true,
        data: {
          level: 'city',
          mapData: processedCityData,
          summary: {
            total_cities: processedCityData.length,
            total_visits: processedCityData.reduce((sum: number, item: any) => sum + item.visits, 0),
            total_users: processedCityData.reduce((sum: number, item: any) => sum + item.unique_users, 0),
            provinces_covered: new Set(processedCityData.map((item: any) => item.province)).size,
            most_active_city: processedCityData[0]?.name || null
          },
          queryInfo: {
            timeRange,
            startDate,
            endDate,
            level,
            minVisits,
            generatedAt: new Date().toISOString()
          }
        },
        message: "获取城市地图数据成功"
      } as ApiResponse;
    
    } else {
      // 详细级别数据（包含更多统计信息）- 使用ip_addresses表
      const [detailMapData] = await pool.execute(`
        SELECT 
          province,
          city,
          adcode,
          COUNT(DISTINCT ip) as unique_ips,
          COUNT(*) as total_visits,
          COUNT(DISTINCT userId) as unique_users,
          COUNT(CASE WHEN userId IS NOT NULL THEN 1 END) as logged_visits,
          COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as visits_today,
          COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as visits_week,
          MIN(created_at) as first_visit,
          MAX(created_at) as last_visit,
          AVG(CASE WHEN userId IS NOT NULL THEN 1 ELSE 0 END) as login_rate,
          GROUP_CONCAT(DISTINCT ip ORDER BY ip SEPARATOR ',') as sample_ips
        FROM ip_addresses
        ${timeCondition} AND province IS NOT NULL AND city IS NOT NULL AND province != '' AND city != ''
        GROUP BY province, city, adcode
        HAVING total_visits >= ?
        ORDER BY unique_users DESC, total_visits DESC
        LIMIT 500
      `, [...timeParams, parseInt(minVisits as string)]) as [RowDataPacket[], any];

      const processedDetailData = detailMapData.map((item: any) => ({
        name: `${item.province}-${item.city}`,
        province: item.province,
        city: item.city,
        adcode: item.adcode,
        value: item.unique_users,
        visits: item.total_visits,
        unique_ips: item.unique_ips,
        unique_users: item.unique_users,
        logged_visits: item.logged_visits,
        visits_today: item.visits_today,
        visits_week: item.visits_week,
        login_rate: parseFloat((item.login_rate * 100).toFixed(2)),
        visit_density: parseFloat((item.total_visits / item.unique_ips).toFixed(2)),
        user_conversion_rate: parseFloat(((item.unique_users / item.unique_ips) * 100).toFixed(2)),
        activity_score: parseFloat(((item.visits_week / Math.max(item.total_visits, 1)) * 100).toFixed(2)),
        sample_ips: item.sample_ips ? item.sample_ips.split(',').slice(0, 5) : [], // 只显示前5个IP样本
        first_visit: item.first_visit,
        last_visit: item.last_visit
      }));

      ctx.body = {
        success: true,
        data: {
          level: 'detail',
          mapData: processedDetailData,
          summary: {
            total_locations: processedDetailData.length,
            total_visits: processedDetailData.reduce((sum: number, item: any) => sum + item.visits, 0),
            total_users: processedDetailData.reduce((sum: number, item: any) => sum + item.unique_users, 0),
            avg_activity_score: parseFloat((processedDetailData.reduce((sum: number, item: any) => sum + item.activity_score, 0) / processedDetailData.length).toFixed(2)),
            high_activity_locations: processedDetailData.filter((item: any) => item.activity_score > 50).length
          },
          queryInfo: {
            timeRange,
            startDate,
            endDate,
            level,
            minVisits,
            generatedAt: new Date().toISOString()
          }
        },
        message: "获取详细地图数据成功"
      } as ApiResponse;
    }
    
  } catch (error: any) {
    console.error("获取地图数据失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "获取地图数据失败",
      error: error.message
    } as ApiResponse;
  }
});

// 专门的DeepSeek余额查询接口
router.get("/deepseek/balance", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    console.log('🎯 专门查询DeepSeek余额接口被调用');
    
    const balanceInfo = await getDeepSeekBalance();
    const usageStats = await getDeepSeekUsageStats([30]); // 默认30天统计
    
    // 额外获取模型使用统计
    const [modelUsageStats] = await pool.execute(`
      SELECT 
        COUNT(*) as total_api_calls,
        COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN 1 END) as calls_last_hour,
        COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY) THEN 1 END) as calls_today,
        COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as calls_week,
        MAX(created_at) as last_api_call
      FROM ai_chat_records 
      WHERE message_type = 'assistant'
    `) as [RowDataPacket[], any];

    ctx.body = {
      success: true,
      data: {
        // 余额信息
        balance: balanceInfo,
        
                 // 使用统计
         usage: {
           ...usageStats,
           api_calls: modelUsageStats[0],
           efficiency: {
             cost_per_request: balanceInfo.total_balance_cny !== 'N/A' && (usageStats.total_requests || 0) > 0 
               ? (parseFloat(balanceInfo.total_balance_cny) / (usageStats.total_requests || 1)).toFixed(4)
               : 'N/A',
             requests_per_day_avg: (usageStats.total_requests || 0) > 0 
               ? ((usageStats.total_requests || 0) / 30).toFixed(2)
               : 0
           }
         },
        
        // 配置和健康状态
        service: {
          model: config.deepseek.model,
          base_url: config.deepseek.base_url,
          api_key_masked: config.deepseek.apiKey ? 
            config.deepseek.apiKey.substring(0, 8) + '...' + config.deepseek.apiKey.substring(config.deepseek.apiKey.length - 4) 
            : 'N/A',
          status: balanceInfo.api_status,
          last_check: balanceInfo.last_updated,
          health_indicators: {
            balance_sufficient: balanceInfo.is_available,
            recent_activity: modelUsageStats[0].calls_last_hour > 0,
            service_accessible: balanceInfo.api_status === 'success'
          }
        },
        
        // 建议和警告
        recommendations: generateDeepSeekRecommendations(balanceInfo, usageStats, modelUsageStats[0])
      },
      message: "DeepSeek余额和使用统计获取成功"
    } as ApiResponse;
    
  } catch (error: any) {
    console.error("DeepSeek余额查询失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "DeepSeek余额查询失败",
      error: error.message
    } as ApiResponse;
  }
});

// 生成DeepSeek使用建议
function generateDeepSeekRecommendations(balanceInfo: any, usageStats: any, apiCallStats: any) {
  const recommendations = [];
  const warnings = [];
  const tips = [];

  // 余额检查
  if (balanceInfo.api_status === 'success') {
    const totalBalance = parseFloat(balanceInfo.total_balance_cny || '0');
    
    if (totalBalance < 10) {
      warnings.push('余额不足10元，建议及时充值以避免服务中断');
    } else if (totalBalance < 50) {
      recommendations.push('余额较低，建议适时充值');
    } else {
      tips.push(`当前余额充足（${balanceInfo.total_balance_cny} CNY）`);
    }

         // 使用频率分析
     const dailyAvgRequests = (usageStats.total_requests || 0) / 30;
     if (dailyAvgRequests > 100) {
       recommendations.push('使用频率较高，建议关注余额变化');
     }

     // 成本分析
     if (totalBalance > 0 && (usageStats.total_requests || 0) > 0) {
       const costPerRequest = totalBalance / (usageStats.total_requests || 1);
       if (costPerRequest > 0.1) {
         tips.push('平均每次请求成本较高，可考虑优化prompt长度');
       }
     }
  } else {
    warnings.push('API连接异常，请检查网络和配置');
  }

  // 服务活跃度
  if (apiCallStats.calls_last_hour === 0 && apiCallStats.calls_today > 0) {
    tips.push('最近一小时无API调用，系统运行正常');
  } else if (apiCallStats.calls_last_hour > 0) {
    tips.push(`最近一小时有${apiCallStats.calls_last_hour}次API调用，系统活跃`);
  }

  return {
    warnings,
    recommendations,
    tips,
    overall_status: warnings.length > 0 ? 'warning' : recommendations.length > 0 ? 'info' : 'good'
  };
}

export default router;
